We’ll be creating a couple of dataviz using Datawrapper and Flourish. Sign up for accounts in both.

Start with the FBI data, which comes in a PDF format

Convert it with Tabula.

Alternatively, you can download the parsed and cleaned version it from Buzzfeed.

Right click and save this file to your computer

Lots of data in here.

There are many variables in here broken out by state and by month.

What questions can we ask it?

What sorts of summaries could we come up with?

Let’s start broadly.

How have background checks evolved over time?

We can pivot table that.

Months and sum of total permits should give us what we need.

Let’s chart it out with a the dataviz service: Datawrapper.

Time series with annotation

Datawrapper is a free (for now) data viz service tool created by someone who used to work at the New York Times.

Like plot.ly, Datawrapper has a section where they show off charts others have made and give them the option to “clone” it or see how they put it together so they can apply it to other data.

We have data for a time series chart and thinking ahead, we probably want to annotate it.

We’re going to emulate this one found in the river section.

drawing

Log into Datawrapper and click on + New Chart at the top.

Copy and paste the data over. Rename the headers generated by the pivot table to “Month” and “Checks” and preserve the spacing of the text.

Fortunately, Datawrapper is smart enough to detect that the data in the “Month” column are dates. Otherwise, you could manually tell Datawrapepr what the data type is by selecting it in the pulldown menu.

Click the Proceed button on the left.

Nice start.

What are those little peaks it seems that happens every year?

There’s not enough information at the moment– it just gives a year and the number of checks.

Click on the Refine tab and under the Configure x-axis subsection, select “2015, Feb, Mar” from the Tick format pulldown menu.

Okay, that’s better. When the mouse hovers over the line it now includes month with the year.

Every year, there’s a spike in December for the holidays, presumably.

One thing is bothering me. This outlier.

Click on Back on the bottom (twice) and delete that November 1998 row.

Now proceed back to the chart and select Annotate and fill in all the details:

Title: Firearm background checks over time

Data source: NICS

Link to data source: https://www.fbi.gov/file-repository/nics_firearm_checks_-_month_year_by_state_type.pdf/view

Byline: Your name

Alright, that’s a chart.

But what does it show?

That background checks are going up over time, which indicates more gun owners.

But by itself, this chart is shallow. A reader will naturally ask why. It needs context.

What explains those significantly large spikes?

Keep note of the peaks: November 2008, December 2012, December 2015.

What events in this spreadsheet line up with those dates?

Let’s add some annotations.

Go to the Highlight value ranges subsection under the Annotate tab.

Click + Add range highlight and select the first peak: 2008, November.

Change the color to red and move the opacity bar to midway.

Manually add the rest and fix the color and opacity to match:

Go to the Text annotations subsection under the Annotate tab.

Click where to add the text so it makes sense.

Add these text points where appropriate.

Important: To fit these text annotations, you’ll need to go back to the Refine tab and change Configure y-axis under Custom range: to 4,000,000.

drawing

This is how all those adjustments should look:

Click on the Design tab and you should get some basic layout options. If you’re premium, you can alter this so it has some custom colors based on values you preset.

Click the Publish button.

Publish one more time to get the share url and the embed code.

Small multiples charts

Let’s break it down to see if we can see deeper patterns.

We’ll use Flourish to create small multiples of the timeline, breaking out each state individually.

First, we need to prepare the data some more.

Let’s get rid of all columns except month, state, and total.

Now, let’s reshape the data so the states are in the columns. Flourish needs wide data, not tall data, for small multiples.

We’ll use our favorite tool: Pivot tables.

Move state into Columns and month into Rows and Sum of totals in Values.

Nice.

Move into a new sheet and clean it up:

MORE stuff we have to do:

Copy and paste values of the new column.

Save the sheet as state_backgrounds.xlsx

Log into Flourish and in projects click the New button under Visualizations.

Select line, bar and pie charts.

Click on the Data tab.

Click on Import. Upload state_backgrounds.xlsx.

Select the state_backgrounds sheet only. (Yes to overwrite current sheet)

Next to Labels type in B because that’s the column where the labels are.

Next to Values type in C-BB because that’s where the values are for the states.

Now click on the Preview tab.

Make sure you have Grid of charts view on.

The dots get in the way a bit. Go to Chart Styles on the right and change Radius under Dots from 3 to 0.

That’s better.

Alright, this is a start.

Name the viz “Background checks by state”

What can we see?

Kentucky’s really spiked over time.

North Carolina had a huge spike but maybe that’s an outlier?

Same with Utah. Seems outlier-ish.

Illinois background checks have been increasing steadily.

What else?

Small states like North Dakota and Montana look like a low line so maybe the checks need to be adjusted?

Or maybe instead of all 50, there’s a logical way to narrow down to a handful of states?

Just things to consider.